/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import Model.Sach;
import Model.Thanhvien;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author Loc Vo
 */
public class SachDAO {

    public static final String SACH_THEM =
            "INSERT INTO SACH "
            + "(MASACH,TENSACH,MATG,MATL,MANXB,NGAYXUATBAN,GIABAN,SOLUONGHIENCO) "
            + "VALUES(?,?,?,?,?,?,?,?)";
    public static final String SACH_XOA =
            "DELETE FROM SACH "
            + "WHERE MASACH = ?";
    public static final String NXB_THEM =
            "INSERT INTO  NHAXUATBAN "
            + "(MANXB,TENNXB,DIACHI,SDT) "
            + "VALUES(?,?,?,?)";
    public static final String NXB_XOA =
            "DELETE FROM NHAXUATBAN "
            + "WHERE MANXB = ?";
    public static final String TACGIA_XOA =
            "DELETE FROM TACGIA "
            + "WHERE MATG = ?";
    public static final String TACGIA_THEM =
            "INSERT INTO TACGIA "
            + "(MATG,TENTG) "
            + "VALUES(?,?)";
    public static final String THELOAI_XOA =
            "DELETE FROM THELOAI "
            + "WHERE MATG = ?";
    public static final String THELOAI_THEM =
            "INSERT INTO THELOAI "
            + "(MATL,TENTL) "
            + "VALUES(?,?)";
    public static final String THANHVIEN_XOA =
            "DELETE FROM THANHVIEN "
            + "WHERE MATHANHVIEN = ?";
    public static final String THANHVIEN_THEM =
            "INSERT INTO THANHVIEN "
            + "(THANHVIEN,TENDANGNHAP,MATKHAU,TENTV,GIOITINH,DIACHI,EMAIL,SDT) "
            + "VALUES(?,?,?,?,?,?,?,?)";
    public static final String DOC_BANG_TACGIA =
            "SELECT MASACH, TENSACH,TENTG,TENTL, TENNXB , NGAYXUATBAN,GIABAN,SOLUONGHIENCO "
            + "FROM SACH "
            + "join NHAXUATBAN on NHAXUATBAN.MANXB= SACH.MANXB "
            + "join TACGIA on TACGIA.MATG = SACH.MATG "
            + "join THELOAI on THELOAI.MATL = SACH.MATL "
            + "WHERE TACGIA.TENTG like ? ";
    public static final String DOC_BANG_TENSACH =
            "SELECT MASACH,TENSACH,TENTG,TENTL,TENNXB,NGAYXUATBAN,GIABAN,SOLUONGHIENCO "
            + "FROM SACH "
            + "join NHAXUATBAN on NHAXUATBAN.MANXB= SACH.MANXB "
            + "join TACGIA on TACGIA.MATG = SACH.MATG "
            + "join THELOAI on THELOAI.MATL = SACH.MATL "
            + "WHERE SACH.TENSACH like ? ";
    public static final String DOC_BANG_THELOAI =
            "SELECT MASACH,TENSACH,TENTG,TENTL,TENNXB,NGAYXUATBAN,GIABAN,SOLUONGHIENCO "
            + "FROM SACH "
            + "join NHAXUATBAN on NHAXUATBAN.MANXB= SACH.MANXB "
            + "join TACGIA on TACGIA.MATG = SACH.MATG "
            + "join THELOAI on THELOAI.MATL = SACH.MATL "
            + "WHERE THELOAI.TENTL like ? ";
    public static final String TATCA_SACH =
            "SELECT MASACH,TENSACH,TENTG,TENTL,TENNXB,NGAYXUATBAN,GIABAN,SOLUONGHIENCO "
            + "FROM SACH "
            + "join NHAXUATBAN on NHAXUATBAN.MANXB= SACH.MANXB "
            + "join TACGIA on TACGIA.MATG = SACH.MATG "
            + "join THELOAI on THELOAI.MATL = SACH.MATL ";
    public static final String TATCA_THANHVIEN =
            "Select * "
            + "from THANHVIEN ";
    public static final String DOC_TEN_TV_DANGNHAP =
            "Select * "
            + "from THANHVIEN "
            + "where  TENDANGNHAP like ?";
    public static final String DOC_TEN_TV =
            "Select * "
            + "from THANHVIEN "
            + "where  TENTV like ?";
    public static final String DOC_TEN_TV_ID =
            "Select * "
            + "from THANHVIEN "
            + "where  MATHANHVIEN like ?";
    public static final String DOC_SACH_BAN_CHAY_NHAT =
            "SELECT TOP ? MASACH,TENSACH,TENTG,TENTL,TENNXB,NGAYXUATBAN,GIABAN,SOLUONGHIENCO "
            + "FROM SACH "
            + "join NHAXUATBAN on NHAXUATBAN.MANXB= SACH.MANXB "
            + "join TACGIA on TACGIA.MATG = SACH.MATG "
            + "join THELOAI on THELOAI.MATL = SACH.MATL ";
    private JdbcTemplate jdbcTemplate;
    private DataSource dataSource;
    

    public List<Sach> allBooks() {
        List<Sach> result = jdbcTemplate.query(TATCA_SACH, new RowMapper<Sach>() {
            @Override
            public Sach mapRow(ResultSet resultSet, int i) throws SQLException {
                return //                        resultSet.getString(1);
                        new Sach(
                        resultSet.getString("MASACH"),
                        resultSet.getString("TENSACH"),
                        resultSet.getString("TENTG"),
                        resultSet.getString("TENTL"),
                        resultSet.getString("TENNXB"),
                        resultSet.getDate("NGAYXUATBAN"),
                        resultSet.getFloat("GIABAN"),
                        resultSet.getInt("SOLUONGHIENCO"));
            }
        });
        return result;
    }

    public List<Sach> getSachBang(final String tukhoa, final String SQL) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, "%" + tukhoa + "%");
                return pst;
            }
        };
        List<Sach> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Sach>() {
            @Override
            public Sach mapRow(ResultSet resultSet, int i) throws SQLException {
                return new Sach(
                        resultSet.getString("MASACH"),
                        resultSet.getString("TENSACH"),
                        resultSet.getString("TENTG"),
                        resultSet.getString("TENTL"),
                        resultSet.getString("TENNXB"),
                        resultSet.getDate("NGAYXUATBAN"),
                        resultSet.getFloat("GIABAN"),
                        resultSet.getInt("SOLUONGHIENCO"));
            }
        });
        return result;
    }

    public List<Thanhvien> getTatCaThanhVien() {

        List<Thanhvien> result = jdbcTemplate.query(TATCA_THANHVIEN, new RowMapper<Thanhvien>() {
            @Override
            public Thanhvien mapRow(ResultSet resultSet, int i) throws SQLException {
                return //                        resultSet.getString(1);
                        new Thanhvien(
                        resultSet.getString("MATHANHVIEN"),
                        resultSet.getString("TENDANGNHAP"),
                        resultSet.getString("MATKHAU"),
                        resultSet.getString("TENTV"),
                        resultSet.getString("GIOITINH"),
                        resultSet.getString("DIACHI"),
                        resultSet.getString("EMAIl"),
                        resultSet.getString("SDT"));
            }
        });
        return result;
    }

    public List<Thanhvien> getThanhVien(final String tukhoa, final String SQL) {

        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, "%" + tukhoa + "%");
                return pst;
            }
        };

        List<Thanhvien> result = jdbcTemplate.query(preparedStatementCreator, new RowMapper<Thanhvien>() {
            @Override
            public Thanhvien mapRow(ResultSet resultSet, int i) throws SQLException {
                return //                        resultSet.getString(1);
                        new Thanhvien(
                        resultSet.getString("MATHANHVIEN"),
                        resultSet.getString("TENDANGNHAP"),
                        resultSet.getString("MATKHAU"),
                        resultSet.getString("TENTV"),
                        resultSet.getString("GIOITINH"),
                        resultSet.getString("DIACHI"),
                        resultSet.getString("EMAIl"),
                        resultSet.getString("SDT"));
            }
        });
        return result;
    }

    public List<Sach> mostSaleBook(int top) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void themSach(final String MASACH, final String TENSACH,
            final String MATG, final String MATL, final String MANXB,
            final Date NGAYXUATBAN,
            final float GIABAN, final int SOLUONGHIENCO) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(SACH_THEM, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MASACH);
                pst.setString(2, TENSACH);
                pst.setString(3, MATG);
                pst.setString(4, MATL);
                pst.setString(5, MANXB);
                pst.setDate(6, NGAYXUATBAN);
                pst.setFloat(7, GIABAN);
                pst.setInt(8, SOLUONGHIENCO);
                return pst;
            }
        };
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
    }

    public void xoaSach(final String Masach) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(SACH_XOA, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, Masach);
                return pst;
            }
        };
        jdbcTemplate.update(preparedStatementCreator);
    }

    public void themNXB(final String MANXB, final String TENNXB,
            final String DIACHI, final String SDT) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(NXB_THEM, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MANXB);
                pst.setString(2, TENNXB);
                pst.setString(3, DIACHI);
                pst.setString(4, SDT);
                return pst;
            }
        };
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
    }

    public void xoaNXB(final String MaNXB) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(NXB_XOA, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MaNXB);
                return pst;
            }
        };
        jdbcTemplate.update(preparedStatementCreator);
    }

    public void themTG(final String MATG, final String TENTG) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(TACGIA_THEM, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MATG);
                pst.setString(2, TENTG);
                return pst;
            }
        };
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
    }

    public void xoaTG(final String MaTG) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(TACGIA_XOA, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MaTG);
                return pst;
            }
        };
        jdbcTemplate.update(preparedStatementCreator);
    }

    public void themTV(final String THANHVIEN, final String TENDANGNHAP,
            final String MATKHAU, final String TENTV, final String GIOITINH,
            final String DIACHI, final String EMAIL, final String SDT) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(THANHVIEN_THEM, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, THANHVIEN);
                pst.setString(2, TENDANGNHAP);
                pst.setString(3, MATKHAU);
                pst.setString(4, TENTV);
                pst.setString(5, GIOITINH);
                pst.setString(6, DIACHI);
                pst.setString(7, EMAIL);
                pst.setString(8, SDT);
                return pst;
            }
        };
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
    }

    public void xoaTV(final String MaTG) {
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement pst = connection.prepareStatement(THANHVIEN_XOA, Statement.RETURN_GENERATED_KEYS);
                pst.setString(1, MaTG);
                return pst;
            }
        };
        jdbcTemplate.update(preparedStatementCreator);
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}
